#!/usr/bin/python3
'''
这个文件涉及到与数据库的操作，该文件中涉及到数据库的组织请查看详细的文档设计
包含一个类 CloudDB
'''

import pymysql as pm#python连接数据库的库

class CloudDB():
    def __init__(self): 
        self.conn = pm.connect(host="127.0.0.1", 
                             port=3306,
                             user="ubuntu",password="ubuntu",
                             database="cloudpre",
                             charset="utf8",
                             autocommit=True)
        self.cursor = self.conn.cursor()

    def db_insert_kfrag(self, delegator, delegatee, filename, kfrag):
        sql = "INSERT INTO sharedfiles (delegator, delegatee, filename, kfrag) VALUES (%s, %s, %s, %s);"
        try:
            self.cursor.execute(sql, [delegator, delegatee, filename, kfrag]) # args (tuple, list or dict) 构造成元组和列表形式，不能把变量分开)
            self.conn.commit()
        except Exception as e:
            print(repr(e))
            self.conn.rollback() # Rollback in case there is any error

    def db_select_kfrag(self, delegator, delegatee, filename):
        sql = "SELECT kfrag FROM sharedfiles WHERE delegator=%s AND delegatee=%s AND filename=%s;"
        try:
            self.cursor.execute(sql, [delegator, delegatee, filename]) # args (tuple, list or dict) 构造成元组和列表形式，不能把变量分开)
            self.conn.commit()
        except Exception as e:
            print(repr(e))
            self.conn.rollback() # Rollback in case there is any error
        kfrags = self.cursor.fetchall() # 不管fetchone还是fetchall都是元组
        return kfrags

    def db_delete_kfrag(self, delegator, filename):
        sql = "DELETE FROM sharedfiles WHERE delegator=%s AND filename=%s;"
        try:
            self.cursor.execute(sql, [delegator, filename]) # args (tuple, list or dict) 构造成元组和列表形式，不能把变量分开)
            self.conn.commit()
        except Exception as e:
            print(repr(e))
            self.conn.rollback() # Rollback in case there is any error

    def db_close(self):
        self.conn.close()

# t = CloudDB()
# t.db_insert_kfrag('alice', 'bob', 'af6495582e27581b368f3587ba47119530709002ccfbcbeab503896251509db4', )


